package com.gm.sfc.db;

import static com.gm.sfc.util.consts.Consts.*;

import java.io.File;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.gm.sfc.event.CursorListener;
import com.gm.sfc.event.QueryEvent;
import com.gm.sfc.field.IField;
import com.gm.sfc.util.LogUtil;

/**
 * データベース操作する
 * @author $Author: segen.der.gottin@gmail.com $
 * @version $Revision: 11 $
 */
public class DatabaseHelper {
    private static String DATABASE_NAME = DB_NAME;
    private SQLiteDatabase db;
    private Context mContext;
    private static DatabaseHelper databaseHelper;
    private int DB_MODE = Context.MODE_PRIVATE;
    private static final String Tag = "DATABASE";

    /**
     * コンストラクタ
     * @param c
     */
    public DatabaseHelper(Context c) {
        mContext = c;
    }

    /**
     * インスタンスを取得
     * @param c
     * @return
     */
    public static DatabaseHelper getInstance(Context c) {
        if (databaseHelper == null) {
            databaseHelper = new DatabaseHelper(c);
        }
        return databaseHelper;
    }

    /**
     * データベースオープン
     * @return
     */
    public boolean open() {
        LogUtil.logD(Tag, "called open");
        boolean flag = false;
        try {
            if (db == null || !db.isOpen()) {
                db = mContext.openOrCreateDatabase(DATABASE_NAME, DB_MODE, null);
            }
            LogUtil.logD(Tag, "database/" + DATABASE_NAME + " opened");
            flag = true;
        } catch (Exception ex) {
            flag = false;
        }
        return flag;
    }

    /**
     * データベースオープン(Assetsコピーロジックを使用していた場合)
     * @deprecated open()を使用してください
     * @return
     */
    public boolean openOld() {
        LogUtil.logD(Tag, "called open");
        boolean flag = false;
        try {
            if (db == null || !db.isOpen()) {
                // ファイルObjectのパスを取得し、パスをもとにDBを開く
                // 参考:http://y-anz-m.blogspot.com/2011/01/android-sqline-database.html<br>
                // ※上記URLコメント内容のArrowsシリーズ対応版
                File fileObj = mContext.getFileStreamPath(DATABASE_NAME);
                String dbPath = fileObj.getAbsolutePath();
                db = SQLiteDatabase.openDatabase(dbPath, null,
                        SQLiteDatabase.OPEN_READWRITE);
            }
            LogUtil.logD("open:" + db.getPath());
            LogUtil.logD(Tag, "database/" + DATABASE_NAME + " opened");
            flag = true;
        } catch (Exception ex) {
            flag = false;
        }
        return flag;
    }

    /**
     * データベースを閉じる
     */
    public void close() {
        LogUtil.logD(Tag, "call close " + DATABASE_NAME);
        if (db != null)
            db.close();
        db = null;
        mContext = null;
        LogUtil.logD(Tag, DATABASE_NAME + " closed ");

    }

    /**
     * テーブルを削除する
     * @param tableName
     * @return
     */
    public IField dropTable(IField tableName) {
        LogUtil.logD(Tag, "called dropTable");
        if (tableName == null) {
            LogUtil.logE(Tag, "table name is error! " + tableName + " is null !");
            return null;
        }

        String DROP_TABLE = " DROP TABLE IF EXISTS " + tableName;

        db.execSQL(DROP_TABLE);

        LogUtil.logD(Tag, "table/" + tableName + " is dropped successfully");

        return tableName;
    }

    /**
     * @param tableDefine
     * @param tableName
     * @return
     */
    public void createTable(String tableDefine) {
        db.execSQL(tableDefine);
    }

    /**
     * @return
     */
    public SQLiteDatabase getDBInstance() {
        return db;
    }

    /**
     * SQL実行
     * @param sql
     */
    public void execSelect(String sql, CursorListener cursorListener) {
        LogUtil.logD(Tag, "Start");
        int count = 0;
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql.toString(), null);
            while (cursor.moveToNext()) {
                count++;
                QueryEvent queryEvt = new QueryEvent(cursor);
                cursorListener.onEachQueryEvent(queryEvt);
            }
            LogUtil.logD(Tag, "SQL Count:" + count);
            QueryEvent queryEvt = new QueryEvent(cursor);
            cursorListener.onFinishQueryEvent(queryEvt);
        } catch (Exception ex) {
            LogUtil.logStack(Tag, ex);
        } finally {
            if (cursor != null)
                cursor.close();
        }
    }

    /**
     * @param SQL
     * @return
     */
    public boolean execSQL(String SQL) {
        if (SQL == null)
            return false;

        boolean flag = false;
        LogUtil.logD(Tag, "called execSQL()");
        LogUtil.logD(Tag, "SQL:" + SQL);
        try {
            db.execSQL(SQL);
            LogUtil.logD(Tag, "sql exec successfully!");
            flag = true;
        } catch (Exception e) {
            LogUtil.logE(Tag, "sql exec not successfully!");
            LogUtil.logStack(Tag, e);
            flag = false;
        }
        return flag;
    }

    /**
     * トランスを実行
     */
    public void beginTrans() {
        LogUtil.logD(Tag, "called beginTrans()");
        db.beginTransaction();
        LogUtil.logD(Tag, "beginTrans() completed");
    }

    /**
     * コミットする
     */
    public void setTransSuccessfull() {
        LogUtil.logD(Tag, "called setTransSuccessfull()");
        db.setTransactionSuccessful();
        LogUtil.logD(Tag, "setTransSuccessfull() completed");
    }

    /**
     * トランザクションを終了する
     */
    public void setEndTransaction() {
        LogUtil.logD(Tag, "called setEndTransaction()");
        db.endTransaction();
        LogUtil.logD(Tag, "setEndTransaction() completed");
    }

    /**
     * SQL　INSERTする　
     * @param sqls
     * @return
     */
    public boolean InsertSql(String sqls) {
        LogUtil.logD(Tag, "called InsertSql()");
        db.beginTransaction();
        LogUtil.logD(Tag, "beginTransaction");
        try {
            LogUtil.logD(Tag, sqls);
            SQLiteStatement stmt = db.compileStatement(sqls);
            stmt.executeInsert();
            LogUtil.logD(Tag, "Insert completed");

            db.setTransactionSuccessful();
            LogUtil.logD(Tag, "Transaction successfully completed");
            return true;
        } catch (Exception ex) {
            LogUtil.logE(Tag, "CATCH :" + ex.getMessage());
            return false;
        } finally {
            db.endTransaction();
            LogUtil.logD(Tag, "end Transaction successfully");
        }
    }

    /**
     * 画像を保存する
     * @param table
     * @param values
     * @param whereClause
     * @return
     */
    public boolean updateImage(String table, ContentValues values, String whereClause) {
        LogUtil.logD(Tag, "called updateImnage");
        db.beginTransaction();
        LogUtil.logD(Tag, "beginTransaction");
        LogUtil.logD(Tag, "values:" + values.toString() + "where:" + whereClause);

        try {
            db.update(table, values, whereClause, null);
            db.setTransactionSuccessful();
            LogUtil.logD(Tag, "Transaction Image Update Successfully");
            return true;
        } catch (Exception ex) {
            LogUtil.logE(Tag, "CATCH :" + ex.getMessage());
            return false;
        } finally {
            db.endTransaction();
            LogUtil.logD(Tag, "end Transaction successfully");
        }
    }

    /**
     * 画像保存
     * @param table
     * @param values
     * @return
     */
    public boolean insertImage(String table, ContentValues values) {
        LogUtil.logD(Tag, "called insert Image");
        db.beginTransaction();
        LogUtil.logD(Tag, "beginTransaction");
        LogUtil.logD(Tag, "values:" + values.toString());
        try {
            db.insert(table, null, values);
            db.setTransactionSuccessful();
            LogUtil.logD(Tag, "Transaction Image Insert Successfully");
            return true;
        } catch (Exception ex) {
            LogUtil.logE(Tag, "CATCH :" + ex.getMessage());
            return false;
        } finally {
            db.endTransaction();
            LogUtil.logD(Tag, "end Transaction successfully");
        }
    }

    /**
     * DBのバージョンを取得
     * @return
     */
    public String getDbVersion() {
        open();
        return String.valueOf(db.getVersion());
    }

    /**
     * DBのバージョンをInteger型で取得
     * @return
     */
    public int getDbVersionInt() {
        open();
        return db.getVersion();
    }

    /**
     * DBのバージョンを更新
     * @param version
     */
    public void upgradeDbVer(int version) {
        open();
        beginTrans();
        db.setVersion(version);
        int currentVer = getDbVersionInt();
        if (currentVer == version) {
            setTransSuccessfull();
        }
        setEndTransaction();
    }
}